package com.hyjx.framework.component.customquery;

import com.alibaba.fastjson.JSON;
import com.hyjx.framework.common.CommonUtil;
import com.hyjx.framework.common.util.StringHelper;
import com.hyjx.framework.component.codetable.CodeTableManager;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 自定义查询配置类
 */
@Controller
@RequestMapping("/customquery")
public class CustomQueryController {
    @Resource
    private CustomQueryService service;
    
    /**
     * 进入到信息查询页面
     *
     * @return
     */
    @RequestMapping(value = "/gotoList", method = RequestMethod.GET)
    public String gotoList(HttpServletRequest request) {
        return "/framework/customquery/list";
    }
    
    @RequestMapping(value = "/queryConfigList", method = RequestMethod.POST)
    @ResponseBody
    public String queryConfigList(CustomQueryModel model, HttpServletRequest request) {
        return service.queryList(model);
    }
    
    /**
     * 执行客户查询操作
     *
     * @param model
     * @param request
     * @return
     */
    @SuppressWarnings("unchecked")
    @RequestMapping(value = "/queryList", method = RequestMethod.POST)
    @ResponseBody
    public String queryList(CustomQueryModel model, HttpServletRequest request) {
        Map<String, String[]> pageMap = request.getParameterMap();
        if (model.getQuery_id() == null || model.getQuery_id().trim().length() == 0)
            return null;
        model = service.getInfoById(model.getQuery_id());
        model.setPageConfig(JSON.parseObject(model.getQuery_config(), PageConfig.class));
        StringBuilder sqlCondition = new StringBuilder(200);
        sqlCondition.append("SELECT * FROM (").append(model.getQuery_sql()).append(") TA WHERE 1=1 ");
        if (!model.getQuery_type().equals("advance"))//高级查询
        {
            for (Map.Entry<String, String[]> entry : pageMap.entrySet()) {
                if (entry != null && entry.getValue() != null && !"".equals(entry.getValue()) && model.getPageConfig() != null && model.getPageConfig().getConditionConfigList() != null) {
                    for (ConditionConfig conditionConfig : model.getPageConfig().getConditionConfigList()) {
                        if (conditionConfig.getColumnName().toLowerCase().equals(entry.getKey().toLowerCase())) {
                            Object valobj = entry.getValue();
                            String val = ((String[]) valobj)[0];
                            if (val != null && val.trim().length() == 0) continue;
                            sqlCondition.append(" ").append(conditionConfig.getSql().replaceAll("##" + entry.getKey().toString() + "##", val.replace("'", "''")));//.append("1 ")
                            break;
                        }
                    }
                }
            }
            /** Append Date */
            if (model.getPageConfig().getConditionConfigList() != null) {
                List<ConditionConfig> listcc = model.getPageConfig().getConditionConfigList();
                Collections.sort(listcc, new SortConditionConfig());
                model.getPageConfig().setConditionConfigList(listcc);
                for (ConditionConfig conditionConfig : model.getPageConfig().getConditionConfigList()) {
                    if ("date".equals(conditionConfig.getType()) && conditionConfig.getSql().indexOf("@@") != -1) {
                        Object cn = pageMap.get(conditionConfig.getColumnName() + "_START");
                        String cn2 = ((String[]) cn)[0];
                        if (cn2 != null && cn2.trim().length() > 0) {
                            
                            sqlCondition.append(" ").append(conditionConfig.getSql().split("@@")[0].replaceAll("##" + conditionConfig.getColumnName() + "_START##", cn2.replace("'", "''"))).append(" ");
                        }
                        Object cn3 = pageMap.get(conditionConfig.getColumnName() + "_END");
                        String cn4 = ((String[]) cn3)[0];
                        if (cn4 != null && cn4.trim().length() > 0) {
                            sqlCondition.append(" ").append(conditionConfig.getSql().split("@@")[1].replaceAll("##" + conditionConfig.getColumnName() + "_END##", cn4.replace("'", "''"))).append(" ");
                        }
                    }
                }
            }
        } else if (model.getQuery_type().equals("advance")) {
            sqlCondition.append(" and");
            Map<String, String[]> params = request.getParameterMap();
            for (Map.Entry<String, String[]> en : params.entrySet()) {
                Object val = en.getValue();
                String vals = ((String[]) val)[0];
                if (vals.length() == 0) continue;
                if (!en.getKey().equals("rows") && !en.getKey().equals("page") && !en.getKey().equals("random") && !en.getKey().equals("query_type") && !en.getKey().equals("query_id")) {
                    if (!en.getKey().startsWith("sel1_") && !en.getKey().startsWith("sel2_") && !en.getKey().startsWith("kh_")) {
                        
                        String[] exp = getExpression(params, en.getKey());
                        if (exp[0] == null && exp[1] == null) continue;
                        sqlCondition.append(this.getLkh(pageMap, en.getKey()));
                        sqlCondition.append(" " + en.getKey().split("@")[0]);
                        if (exp[0].equals("eq"))
                            sqlCondition.append("=");
                        else if (exp[0].equals("neq"))
                            sqlCondition.append("<>");
                        else if (exp[0].equals("like")) {
                            sqlCondition.append(" like ");
                            sqlCondition.append("'%" + vals + "%'");
                        } else if (exp[0].equals("gt"))
                            sqlCondition.append(" > ");
                        else if (exp[0].equals("lt"))
                            sqlCondition.append(" < ");
                        else if (exp[0].equals("gte"))
                            sqlCondition.append(" >= ");
                        else if (exp[0].equals("lte"))
                            sqlCondition.append(" <= ");
                        if (!exp[0].equals("like"))
                            sqlCondition.append("'" + vals + "'");
                        sqlCondition.append(this.getRkh(pageMap, en.getKey()));
                        sqlCondition.append(" ");
                        sqlCondition.append(exp[1]);
                        
                    }
                }
            }
        }
        if (sqlCondition.toString().endsWith(" and") || sqlCondition.toString().endsWith(" or")) {
            sqlCondition.delete(sqlCondition.lastIndexOf(" "), sqlCondition.length());
        }
        
        List<ListConfig> codeCols = new ArrayList<ListConfig>();
        for (ListConfig lc : model.getPageConfig().getListConfigList()) {
            if ("Y".equals(lc.getIsShow())) {
                if (lc.getCodeTable() != null && !"".equals(lc.getCodeTable())) {
                    if ("standard".equals(lc.getType())) {
                        codeCols.add(lc);
                    }

                }
            }
        }
        
        String map = service.queryMapJSON(sqlCondition.toString());
        Object jo = JSON.parse(map);
        com.alibaba.fastjson.JSONObject obj = (com.alibaba.fastjson.JSONObject) jo;
        com.alibaba.fastjson.JSONArray list = (com.alibaba.fastjson.JSONArray) obj.get("rows");
        for (Object j : list) {
            for (Map.Entry<String, Object> e : ((com.alibaba.fastjson.JSONObject) j).entrySet()) {
                for (ListConfig cc : codeCols) {
                    if (e.getKey().equals(cc.getColumnName())) {
                        e.setValue(CodeTableManager.getNameByCode(cc.getCodeTable(), String.valueOf(e.getValue())));
                    }
                }
            }
        }
        map = JSON.toJSONString(jo);
        return map;
    }
    
    private String getLkh(Map<String, String[]> map, String col) {
        String kh = "";
        for (Map.Entry<String, String[]> en : map.entrySet()) {
            Object val = en.getValue();
            if (en.getKey().equals("kh_" + col)) {
                if (((String[]) val)[0].equals("l"))
                    return "(";
            }
        }
        return kh;
    }
    
    private String getRkh(Map<String, String[]> map, String col) {
        String kh = "";
        for (Map.Entry<String, String[]> en : map.entrySet()) {
            Object val = en.getValue();
            if (en.getKey().equals("kh_" + col)) {
                if (((String[]) val)[0].equals("r"))
                    return ")";
            }
        }
        return kh;
    }
    
    /**
     * 根据列取表达式
     *
     * @param map
     * @param col
     * @return
     */
    private String[] getExpression(Map<String, String[]> map, String col) {
        String[] exp = new String[4];
        for (Map.Entry<String, String[]> en : map.entrySet()) {
            Object val = en.getValue();
            if (!en.getKey().equals(col) && en.getKey().equals("sel1_" + col)) {
                exp[0] = ((String[]) val)[0];
            }
        }
        for (Map.Entry<String, String[]> en : map.entrySet())
            if (!en.getKey().equals(col) && en.getKey().equals("sel2_" + col)) {
                Object val = en.getValue();
                exp[1] = ((String[]) val)[0];
            }
//		for (Map.Entry<String, String> en : map.entrySet())
//			if (en.getKey().equals(col)) {
//				Object val = en.getValue();
//				if("l".equals(((String[]) val)[0]))
//					exp[2] = ((String[]) val)[0];
//				if("r".equals(((String[]) val)[0]))
//					exp[3] = ((String[]) val)[0];
//			}
        return exp;
    }
    
    /**
     * 查询列表
     *
     * @param model
     * @param request
     * @return
     */
    @SuppressWarnings("unchecked")
    @RequestMapping(value = "/customqueryList", method = {RequestMethod.POST, RequestMethod.GET})
    public ModelAndView customqueryList(CustomQueryModel model, HttpServletRequest request, HttpServletResponse response) {
        //Map<String,String> pageMap = request.getParameterMap();
        
        
        Map<String, String> pageMap = (Map<String, String>) request.getAttribute("pageMap");
        if (model.getQuery_id() == null || model.getQuery_id().trim().length() == 0)
            return null;
        model = service.getInfoById(model.getQuery_id());
        
        
        model.setPageConfig(JSON.parseObject(model.getQuery_config(), PageConfig.class));
        ModelAndView mav = new ModelAndView("framework/customquery/customequery");
        mav.addObject("model", model);
        
        if ("N".equals(model.getValidity())) {
            request.setAttribute("msg", "错误：无效查询。");
            return mav;
        }
        if (model.getPageConfig().getListConfigList() == null) {
            try {
                response.setContentType("text/html;charset=utf-8");
                response.setCharacterEncoding("utf-8");
                response.getWriter().print("<script>alert('错误：查询配置不正确。');window.close();</script>");
            } catch (IOException e) {
                e.printStackTrace();
            }
            return null;
        }
        String html = "";
        List<ListConfig> listConfigList = model.getPageConfig().getListConfigList();
        String path = request.getContextPath();
        //int i = 0;
        SortListConfig sl = new SortListConfig();
        Collections.sort(listConfigList, sl);
        for (ListConfig listConfig : listConfigList) {
            //i++;
            if ("Y".equals(listConfig.getIsShow())) {
                if ("operation".equals(listConfig.getType())) {
                    String colhtml = listConfig.getColumnName();
                    html = "{field:'" + "" + "',title:'" + listConfig.getTitleName() + "',width:200,";
                    html += "formatter: function (value, rowData, rowIndex) {";
                    colhtml = colhtml.replaceAll("##PATH##", path).replaceAll("&lt;", "<").replaceAll("&gt;", ">")
                            .replaceAll("&amp;", "\\&");
                    colhtml = colhtml.replaceAll("=##", "=\"+rowData.");
                    colhtml = colhtml.replaceAll("##", "+\"");
                    html += "return \"" + colhtml + "\"";
                    html += ";}},";
                } else {
                    html += "{field:'" + listConfig.getColumnName() + "',title:'" + listConfig.getTitleName()
                            + "',width:" + (listConfig.getWidth().equals("") ? 100 : listConfig.getWidth()) + ",align:" + (listConfig.getAlign() + "").replaceAll("style='text-align:", "'").replaceAll(";", "") + "},";
                }
                //html+="\n";
            }
        }
        if (html.endsWith(","))
            html = html.substring(0, html.length() - 1);
        mav.addObject("colhtml", html);
        
        
        String tbodyhtml = "";
        List<ConditionConfig> conditionConfigList = model.getPageConfig().getConditionConfigList();
        int countSpan = 0;
        if (conditionConfigList != null)
            for (ConditionConfig conditionConfig : conditionConfigList) {
                if (countSpan == 0) {
                    tbodyhtml += "<tr>";
                }
                if ("half".equals(conditionConfig.getWidth())) {
                    countSpan++;
                } else if ("total".equals(conditionConfig.getWidth())) {
                    countSpan += 2;
                }
                if (countSpan > 2) {
                    tbodyhtml += "<tr>";
                }
                tbodyhtml += "<td style=\"width:100px;text-align:right\" align=\"right\" >";
                tbodyhtml += conditionConfig.getDescription() + "</td>";
                if ("half".equals(conditionConfig.getWidth())) {
                    tbodyhtml += "<td style=\"width:100px;\">";
                } else {
                    tbodyhtml += "<td colspan=\"3\" >";
                }
                if ("input".equals(conditionConfig.getType())) {
                    tbodyhtml += "<input type=\"text\" class=\"easyui-textbox\"  value=\"" + (pageMap == null || pageMap.get(conditionConfig.getColumnName()) == null ? "" : pageMap.get(conditionConfig.getColumnName())) + "\" name=\"" + conditionConfig.getColumnName() + "\" />";
                } else if ("standard".equals(conditionConfig.getType()) || "config".equals(conditionConfig.getType())) {
                    tbodyhtml += "<select class=\"hy-codeselect\" id=\"pageMap_" + conditionConfig.getColumnName() + "\" name=\"" + conditionConfig.getColumnName() + "\" codetable=\"" + conditionConfig.getCodeTable() + "\"></select>";
                } else if ("date".equals(conditionConfig.getType())) {
                    tbodyhtml += "<input type=\"text\" class=\"easyui-datebox\"  name=\"" + conditionConfig.getColumnName() + "_START\" size=\"12\"  value=\"" + (pageMap == null || pageMap.get(conditionConfig.getColumnName() + "_START") == null ? "" : pageMap.get(conditionConfig.getColumnName() + "_START")) + "\" />";
                    tbodyhtml += "至";
                    tbodyhtml += "<input type=\"text\"  class=\"easyui-datebox\"  name=\"" + conditionConfig.getColumnName() + "_END\" size=\"12\"  value=\"" + (pageMap == null || pageMap.get(conditionConfig.getColumnName() + "_END") == null ? "" : pageMap.get(conditionConfig.getColumnName() + "_END")) + "\" />";
                }
                tbodyhtml += "</td>";
                if ("half".equals(conditionConfig.getWidth()) && conditionConfigList.size() % 2 == 1) {
                    tbodyhtml += "<td></td><td></td>";
                }
                if (countSpan >= 2) {
                    countSpan = 0;
                    tbodyhtml += "</tr>";
                }
            }
        if (countSpan == 1) {
        }
        
        mav.addObject("tbodyhtml", tbodyhtml);
        return mav;
    }
    
    /**
     * 跳转到增加页面
     *
     * @return
     */
    @RequestMapping(value = "/gotoAdd", method = {RequestMethod.GET, RequestMethod.POST})
    public String gotoAdd(HttpServletRequest request) {
        CustomQueryModel model = service.getInfoById(request.getParameter("query_id"));
        if (model != null) {
            model.setAction_type(request.getParameter("action_type"));
        } else {
            model = new CustomQueryModel();
            model.setValidity("Y");
        }
        request.setAttribute("model", model);
        return "/framework/customquery/add";
    }
    
    /**
     * 增加信息
     *
     * @param model
     * @param request
     * @return
     */
    @RequestMapping(value = "/doAdd", method = RequestMethod.POST)
    public ModelAndView doAdd(CustomQueryModel model, HttpServletRequest request) {
        ModelAndView mav = new ModelAndView("/framework/customquery/add");
        PageConfig pageConfig = null;
        if (model.getQuery_id() == null || model.getQuery_id().trim().length() == 0) //新增
        {
            
            pageConfig = service.getPageConfig(model);
            if (pageConfig != null && (pageConfig.getListConfigList() != null && pageConfig.getListConfigList().size() > 0 || pageConfig.getConditionConfigList() != null)) {
                model.setQuery_id(CommonUtil.getUUID32());
                model.setQuery_config("{}");
                model.setCreate_date(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
                service.add(model);
                model.setPageConfig(pageConfig);
            } else {
                mav.addObject("model", model);
                request.setAttribute("msg", "错误：sql语句不正确；请不要在语句末尾加分号、空格等多余符号。");
                return mav;
            }
        } else//修改
        {
            //if(model.getPageConfig()==null || model.getPageConfig().getListConfigList()==null)
            String query_sql_old = request.getParameter("query_sql_old");
            if (model.getPageConfig() == null || model.getPageConfig().getListConfigList() == null) {
                if (model.getQuery_sql().equals(query_sql_old)) //没有修改sql语句
                {
                    
                    CustomQueryModel model2 = service.getInfoById(model.getQuery_id());
                    if (model2.getQuery_config() == null || model2.getQuery_config().trim().length() < 10) {
                        PageConfig pc = service.getPageConfig(model2);
                        if (pc == null) {
                            mav.addObject("model", model);
                            request.setAttribute("msg", "错误：sql语句不正确");
                            return mav;
                        } else
                            model.setQuery_config(JSON.toJSONString(pc));
                    } else
                        model.setQuery_config(model2.getQuery_config()); //.replaceAll("\\$", "&amp;").replaceAll("<","&lt;").replaceAll(">", "&gt;")
                    if (model.getQuery_config() != null && model.getQuery_config().trim().length() > 10)
                        service.edit(model);
                    
                    model.setPageConfig(JSON.parseObject(model.getQuery_config(), PageConfig.class));
                    //				if(model2!=null)
                    //				{
                    //					if(model.getQuery_config() == null)
                    //						model.setQuery_config(JSON.toJSONString(model.getPageConfig())); //.replaceAll("\\$", "&amp;").replaceAll("<","&lt;").replaceAll(">", "&gt;")
                    //					service.edit(model);
                    //					model.setPageConfig(JSON.parseObject(model2.getQuery_config(),PageConfig.class));
                    //				}
                } else {
                    model.setPageConfig(service.getPageConfig(model));
                    service.edit(model);
                    //model.setPageConfig(JSON.parseObject(model2.getQuery_config(),PageConfig.class));
                }
            } else {
                //model.setPageConfig(service.getPageConfig(model));
                model.setQuery_config(JSON.toJSONString(model.getPageConfig())); //.replaceAll("\\$", "&amp;").replaceAll("<","&lt;").replaceAll(">", "&gt;")
                model.setValidity(null);
                service.edit(model);
                mav.addObject("result", "1");
            }
        }
        mav.addObject("model", model);
        return mav;
    }
    
    /**
     * 跳转到修改页面
     * request
     *
     * @return
     */
    @RequestMapping(value = "/gotoEdit", method = {RequestMethod.GET, RequestMethod.POST})
    public ModelAndView gotoEdit(HttpServletRequest request) {
        String id = request.getParameter("id");
        CustomQueryModel model = service.getInfoById(id);
        ModelAndView mav = new ModelAndView("/framework/customquery/add");
        mav.addObject("model", model);
        return mav;
    }
    
    /**
     * 修改信息
     *
     * @param model
     * @return
     */
    @RequestMapping(value = "/doEdit", method = RequestMethod.POST)
    @ResponseBody
    public String doEdit(CustomQueryModel model) {
        if (service.edit(model) > 0) {
            return "success";
        } else {
            return "failure";
        }
    }
    
    /**
     * 删除信息
     * request
     *
     * @return
     */
    @RequestMapping(value = "/gotoDelete", method = RequestMethod.GET)
    @ResponseBody
    public String gotoDelete(HttpServletRequest request) {
        String ids = request.getParameter("ids");
        
        if (service.delete(ids) > 0) {
            return "success";
        } else {
            return "failure";
        }
    }
    
    /**
     * 信息查看
     * request
     *
     * @return
     */
    @SuppressWarnings("unchecked")
    @RequestMapping(value = "/customqueryview", method = RequestMethod.GET)
    public ModelAndView gotoView(HttpServletRequest request) {
        String id = request.getParameter("query_id");
        String recid = request.getParameter("recid");
        CustomQueryModel model = service.getInfoById(id);

        if (model.getValidity().equals("N")) {
            request.setAttribute("msg", "错误：无效查询。");
            return null;
        }
        String sql = model.getQuery_sql().toLowerCase();
        String sqlfrag[] = sql.split("from");
        String afterfrag = sqlfrag[1];
        afterfrag = afterfrag.trim();
        if (afterfrag.indexOf(" ") > -1) {
            afterfrag = afterfrag.split(" ")[0];
        }
        model.setPageConfig(JSON.parseObject(model.getQuery_config(), PageConfig.class));
        if (sql.toLowerCase().indexOf("where") == -1) {
            sql = sql + " where " + model.getPageConfig().getListConfigList().get(0).getColumnName() + "=" + "'" + recid + "'";
        } else
            sql = sql + " and " + model.getPageConfig().getListConfigList().get(0).getColumnName() + "=" + "'" + recid + "'";
        List<Map<String, String>> map = service.queryMap(sql);
        ModelAndView mav = new ModelAndView("/framework/customquery/customequeryview");
        mav.addObject("listconfig", model.getPageConfig().getListConfigList());
        mav.addObject("map", map.get(0));
        return mav;
    }
    
    @RequestMapping(value = "/saveTpl", produces = "text/html;charset=utf-8", method = RequestMethod.POST)
    @ResponseBody
    public String saveTpl(HttpServletRequest request) {
        String tplhtml = request.getParameter("tplhtml");
        String query_id = request.getParameter("query_id");
        CustomQueryTplModel cqt = new CustomQueryTplModel();
        cqt.setId(StringHelper.getPrimaryKey());
        cqt.setQuery_id(query_id);
        cqt.setTpl_data(tplhtml);
        cqt.setLast_date(new Date());
        service.inserttpl(cqt);
        return cqt.getId();
    }
    
    @RequestMapping(value = "/gotoAddTpl", method = {RequestMethod.GET, RequestMethod.POST})
    public ModelAndView gotoAddTpl(HttpServletRequest request) {
        String id = request.getParameter("id");
        
        ModelAndView mav = new ModelAndView("/framework/customquery/addtpl");
        mav.addObject("id", id);
        return mav;
    }
    
    @RequestMapping(value = "/addTpl", produces = "text/html;charset=utf-8", method = RequestMethod.POST)
    @ResponseBody
    public String addTpl(HttpServletRequest request) {
        String id = request.getParameter("id");
        String tpl_name = request.getParameter("tpl_name");
        CustomQueryTplModel cqt = new CustomQueryTplModel();
        cqt.setId(id);
        cqt.setTpl_name(tpl_name);
        service.edittpl(cqt);
        return "";
    }
    
    @RequestMapping(value = "/getTplData", produces = "text/html;charset=utf-8", method = RequestMethod.POST)
    @ResponseBody
    public String getTplData(HttpServletRequest request) {
        CustomQueryTplModel cqt = new CustomQueryTplModel();
        cqt.setQuery_id(request.getParameter("query_id"));
        return service.getTplData(cqt);
    }
    
    @RequestMapping(value = "/getTplById", produces = "text/html;charset=utf-8", method = RequestMethod.POST)
    @ResponseBody
    public String getTplById(HttpServletRequest request) {
        CustomQueryTplModel cqt = new CustomQueryTplModel();
        cqt.setId(request.getParameter("id"));
        return service.getTplById(cqt);
    }
    
    @RequestMapping(value = "/delTpl", produces = "text/html;charset=utf-8", method = RequestMethod.POST)
    @ResponseBody
    public String delTpl(HttpServletRequest request) {
        CustomQueryTplModel cqt = new CustomQueryTplModel();
        cqt.setId(request.getParameter("id"));
        int i = service.delTpl(cqt);
        return i > 0 ? "success" : "false";
    }
    
    
}